﻿using System;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using System.Collections;
using System.IO;
using System.Globalization;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using Microsoft.SqlServer.Management.Trace;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;
using SMO = Microsoft.SqlServer.Management.Smo;
using WMI = Microsoft.SqlServer.Management.Smo.Wmi;
using Model = SQLServerHealthCheck.Model;
using SQLServerHealthCheck.Model;
using AG.Utilities;

namespace SQLServerHealthCheck.SQLServer
{
    public static class SQLTrace
    {

        public static List<Exception> errors;


        private static Regex _binaryConstant = new Regex("0X([0-9ABCDEF])+", RegexOptions.Compiled);
        private static Regex _blockComment = new Regex(@"/\*.*?\*/", RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _blockCommentDelimiters = new Regex(@"/\*|\*/", RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _brackets = new Regex(@"(\[|\])", RegexOptions.Compiled);
        private static Regex _cursor = new Regex(@"EXEC\s+SP_CURSOROPEN\s+(@CURSOR\s*=\s*)?\@P1\s+OUTPUT\,\s*(@STMT\s*=\s*)?(N)?(?<tick>')  (?<statement>      ((  (?!\k<tick>)  .|\k<tick>{2})*)   )    \k<tick>", RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _cursorPrepExec = new Regex("EXEC\r\n\\s+     # any spaces\r\nsp_cursorprepexec\r\n.+       # any characters up to the string\r\nN  \r\n(?<tick>')   # matches an apostraphe\r\n(?!@)    # but no @ following\r\n(?<statement>   ((  (?!\\k<tick>)  .|\\k<tick>{2})*)   )    # all the characters ...\r\n\\k<tick>   # until the next tick that isn't doubled.", RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _dbAndObjectName = new Regex(@"EXEC(UTE){0,1}\s+(?<database>\w+)\.\.(?<object>\w+)", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _delimiterStart = new Regex(@"(--)|(/\*)|'", RegexOptions.Compiled);
        private static Regex _doubleApostrophe = new Regex("('')(?<string>.*?)('')", RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _emptyString = new Regex(@"\'\'", RegexOptions.Compiled);
        private static Regex _execPrepped = new Regex(@"^EXEC\s+SP_EXECUTE\s+(?<stmtnum>\d+)", RegexOptions.Compiled);
        private static Regex _execUnprep = new Regex(@"EXEC\s+SP_UNPREPARE\s+(?<stmtnum>\d+)", RegexOptions.Compiled);
        private static Regex _inClause = new Regex(@"IN\s*\(\s*\{.*\}\s*\)", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _inlineComment = new Regex("--.*$", RegexOptions.Compiled | RegexOptions.Multiline);
        private static Regex _numericConstant = new Regex(@"(?<prefix>[\(\s,=\-><\!\&\|\+\*\/\%\~\$])(?<digits>[\-\.\d]+)", RegexOptions.Compiled);
        private static Regex _objectName = new Regex(@"EXEC(UTE){0,1}\s(?<schema>(\w+\.)*)(?<object>\w+)", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _prepareSql = new Regex(@"SET\s+@P1=(?<stmtnum>(NULL|\d+))\s+EXEC\s+(?<preptype>SP_PREP(ARE|EXEC))\s+@P1\s+OUTPUT,\s*(NULL|(N\'.+?\')),\s*N(?<remaining>.+)$", RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _prepExecRpc = new Regex(@"SET\s+@P1=(?<stmtnum>\d+)\s+EXEC\s+SP_PREPEXECRPC\s+@P1\s+OUTPUT,\s*N\'(?<statement>.+?)'", RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Regex _preppedSqlStatement = new Regex(@"^(')(?<statement>((?!\1).|\1{2})*)\1", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _spaces = new Regex(@"\s+", RegexOptions.Compiled);
        private static Regex _spExecuteSql = new Regex(@"EXEC\s+SP_EXECUTESQL\s+N\'(?<statement>.+?)\'", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _spreadCsv = new Regex(@",(?=\S)", RegexOptions.Compiled);
        private static Regex _stringConstant = new Regex(@"(')(((?!\1).|\1{2})*)\1", RegexOptions.Singleline | RegexOptions.Compiled);
        private static Regex _unicodeConstant = new Regex("N{STR}", RegexOptions.Compiled);
        private const int CURRENT_DATABASE_VERSION = 2;
        private static Hashtable prepSql = new Hashtable();




        public static int ScheduleTrace(Database database, DateTime Start, DateTime End)
        {
            int databaseTraceId = 0;
            try
            {

                //Check the connection to SQL Server Instance and gets the details.
                if (CollectServerAndServiceData.IsSQLServerAccessible(database.Service.GetConnectionString("master")))
                {

                    using (SqlConnection cn = new SqlConnection(database.Service.GetConnectionString("master")))
                    {
                        cn.Open();




                        string sqlOLE2 =


                            @" Select value from   sys.configurations
                          where name = 'show advanced options' or name = 'xp_cmdshell'

                               EXEC sp_configure 'show advanced options', 1
                                RECONFIGURE
                                EXEC sp_configure 'xp_cmdshell', 1
                                RECONFIGURE
                              
                            ";
                        SqlCommand OLEcmd2 = new SqlCommand(sqlOLE2, cn);
                        SqlDataReader thisReader = OLEcmd2.ExecuteReader();
                        List<Int32> settings = new List<Int32>();
                        while (thisReader.Read())
                        {
                            settings.Add(Convert.ToInt32(thisReader["value"].ToString()));

                        }
                        thisReader.Close();
                        string strStartdate = AddPrefixtoString(Start.Year.ToString()) + AddPrefixtoString(Start.Month.ToString()) + AddPrefixtoString(Start.Day.ToString());//stores the start date
                        string strStartTime = AddPrefixtoString(Start.Hour.ToString()) + AddPrefixtoString(Start.Minute.ToString()) + AddPrefixtoString(Start.Second.ToString());//s tores the start time

                        string strEndDate = AddPrefixtoString(End.Year.ToString()) + "-" + AddPrefixtoString(End.Month.ToString()) + "-" + AddPrefixtoString(End.Day.ToString());//stores the end date
                        string strEndTime = AddPrefixtoString(End.Hour.ToString()) + AddPrefixtoString(End.Minute.ToString()) + AddPrefixtoString(End.Second.ToString()); //stores the end time

                        string strStopTime = strEndDate + " " + AddPrefixtoString(End.Hour.ToString()) + ":" + AddPrefixtoString(End.Minute.ToString()) + ":" + AddPrefixtoString(End.Second.ToString());

                        string strStartTimeClr = AddPrefixtoString(End.Hour.ToString()) + AddPrefixtoString((End.Minute + 1).ToString()) + AddPrefixtoString(End.Second.ToString());


                        //inserts the details in DatabaseTrace table in SQLServerHealthCheckDeploy datbase and returns a unique Job Name


                        //Open conection to the SQLServerHealthCheck Deploy database
                        SqlConnection cnHealthCheck = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
                        cnHealthCheck.Open();

                        //insert the details of Trace File into TraceFile table
                        string strTraceFile = @"insert into 
                                                    DatabaseTrace(Databaseid,TraceStart,TraceEnd) 
                                                 values(" + database.DatabaseID + @",@StartDate,@EndDate)
                                                SET @DatabaseTraceID = SCOPE_IDENTITY()";
                        SqlCommand ocmdTrace = new SqlCommand(strTraceFile, cnHealthCheck);

                        ocmdTrace.Parameters.Add("@StartDate", SqlDbType.DateTime);
                        ocmdTrace.Parameters["@StartDate"].Value = Start;

                        ocmdTrace.Parameters.Add("@EndDate", SqlDbType.DateTime);
                        ocmdTrace.Parameters["@EndDate"].Value = End;

                        ocmdTrace.Parameters.Add("@DatabaseTraceID", SqlDbType.Int);
                        ocmdTrace.Parameters["@DatabaseTraceID"].Direction = ParameterDirection.Output;

                        ocmdTrace.ExecuteNonQuery();


                        databaseTraceId = (int)ocmdTrace.Parameters["@DatabaseTraceID"].Value;

                        string jobName = "Capture_Trace_" + databaseTraceId.ToString();

                        //Drops  Proc_CaptureTrace for every job in master database
                        String sqldropproc = @"	
                                                USE [master]

                                                IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_CaptureTrace_" + jobName.Trim() + @"]') AND type in (N'P', N'PC'))
                                                DROP PROCEDURE [dbo].[Proc_CaptureTrace_" + jobName.Trim() + @"]
                                                ";

                        SqlCommand cmd = new SqlCommand(sqldropproc, cn);
                        cmd.ExecuteNonQuery();

                        //Creates   Proc_CaptureTrace uniquely for every job in master database
                        String sqlcreateproc = @" if not exists(Select * FROM sys.procedures where name='Proc_CaptureTrace' )
 begin

EXEC dbo.sp_executesql @statement = N'
 create PROCEDURE [dbo].[Proc_CaptureTrace] @StopTime datetime -- Time when Trace need to be stopped
												,@DatabaseName nvarchar(300)
                                                ,@FileName nvarchar(256)--Trace File Name
                                            , @TraceID int OUTPUT -- Return the TraceID to the caller.
                                            WITH RECOMPILE
                                            AS
                                            -- Create a Queue
                                            declare @rc int
                                            declare @dbaTraceID int
                                            declare @maxfilesize bigint
                                            set @maxfilesize = 5 

                                            -- Please replace the text InsertFileNameHere, with an appropriate
                                            -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
                                            -- will be appended to the filename automatically. If you are writing from
                                            -- remote server to local drive, please use UNC path and make sure server has
                                            -- write access to your network share


                                            exec @rc = sp_trace_create @TraceID output, 0, @FileName, @maxfilesize, @StopTime

                                            if (@rc != 0) goto error 

                                            -- Client side File and Table cannot be scripted

                                            -- Set the events
                                            declare @on bit
                                            set @on = 1
                                            exec sp_trace_setevent @TraceID, 10, 7, @on
                                            exec sp_trace_setevent @TraceID, 10, 15, @on
                                            exec sp_trace_setevent @TraceID, 10, 31, @on
                                            exec sp_trace_setevent @TraceID, 10, 8, @on
                                            exec sp_trace_setevent @TraceID, 10, 16, @on
                                            exec sp_trace_setevent @TraceID, 10, 48, @on
                                            exec sp_trace_setevent @TraceID, 10, 64, @on
                                            exec sp_trace_setevent @TraceID, 10, 1, @on
                                            exec sp_trace_setevent @TraceID, 10, 9, @on
                                            exec sp_trace_setevent @TraceID, 10, 17, @on
                                            exec sp_trace_setevent @TraceID, 10, 25, @on
                                            exec sp_trace_setevent @TraceID, 10, 41, @on
                                            exec sp_trace_setevent @TraceID, 10, 49, @on
                                            exec sp_trace_setevent @TraceID, 10, 2, @on
                                            exec sp_trace_setevent @TraceID, 10, 10, @on
                                            exec sp_trace_setevent @TraceID, 10, 18, @on
                                            exec sp_trace_setevent @TraceID, 10, 26, @on
                                            exec sp_trace_setevent @TraceID, 10, 34, @on
                                            exec sp_trace_setevent @TraceID, 10, 50, @on
                                            exec sp_trace_setevent @TraceID, 10, 66, @on
                                            exec sp_trace_setevent @TraceID, 10, 3, @on
                                            exec sp_trace_setevent @TraceID, 10, 11, @on
                                            exec sp_trace_setevent @TraceID, 10, 35, @on
                                            exec sp_trace_setevent @TraceID, 10, 51, @on
                                            exec sp_trace_setevent @TraceID, 10, 4, @on
                                            exec sp_trace_setevent @TraceID, 10, 12, @on
                                            exec sp_trace_setevent @TraceID, 10, 60, @on
                                            exec sp_trace_setevent @TraceID, 10, 13, @on
                                            exec sp_trace_setevent @TraceID, 10, 6, @on
                                            exec sp_trace_setevent @TraceID, 10, 14, @on
                                            exec sp_trace_setevent @TraceID, 12, 7, @on
                                            exec sp_trace_setevent @TraceID, 12, 15, @on
                                            exec sp_trace_setevent @TraceID, 12, 31, @on
                                            exec sp_trace_setevent @TraceID, 12, 8, @on
                                            exec sp_trace_setevent @TraceID, 12, 16, @on
                                            exec sp_trace_setevent @TraceID, 12, 48, @on
                                            exec sp_trace_setevent @TraceID, 12, 64, @on
                                            exec sp_trace_setevent @TraceID, 12, 1, @on
                                            exec sp_trace_setevent @TraceID, 12, 9, @on
                                            exec sp_trace_setevent @TraceID, 12, 17, @on
                                            exec sp_trace_setevent @TraceID, 12, 41, @on
                                            exec sp_trace_setevent @TraceID, 12, 49, @on
                                            exec sp_trace_setevent @TraceID, 12, 6, @on
                                            exec sp_trace_setevent @TraceID, 12, 10, @on
                                            exec sp_trace_setevent @TraceID, 12, 14, @on
                                            exec sp_trace_setevent @TraceID, 12, 18, @on
                                            exec sp_trace_setevent @TraceID, 12, 26, @on
                                            exec sp_trace_setevent @TraceID, 12, 50, @on
                                            exec sp_trace_setevent @TraceID, 12, 66, @on
                                            exec sp_trace_setevent @TraceID, 12, 3, @on
                                            exec sp_trace_setevent @TraceID, 12, 11, @on
                                            exec sp_trace_setevent @TraceID, 12, 35, @on
                                            exec sp_trace_setevent @TraceID, 12, 51, @on
                                            exec sp_trace_setevent @TraceID, 12, 4, @on
                                            exec sp_trace_setevent @TraceID, 12, 12, @on
                                            exec sp_trace_setevent @TraceID, 12, 60, @on
                                            exec sp_trace_setevent @TraceID, 12, 13, @on


                                            -- Set the Filters
                                            declare @intfilter int
                                            declare @bigintfilter bigint


                                            exec sp_trace_setfilter @TraceID, 35, 0, 6, @DatabaseName;
                     -- Set the trace status to start
                                                exec sp_trace_setstatus @TraceID, 1

                                                -- display trace id for future references
                                                select TraceID=@TraceID

                                                error: 
                                                select ErrorCode=@rc
												'
												END
    ";

                        SqlCommand cmdcreate = new SqlCommand(sqlcreateproc, cn);
                        cmdcreate.ExecuteNonQuery();

                        //Job Exists delete the job
                        String sqlJobDelete = @"USE [msdb]
                            IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'" + jobName.Trim() + @"')
                                EXEC msdb.dbo.sp_delete_job @job_name='" + jobName.Trim() + "', @delete_unused_schedule=1";
                        SqlCommand cmdJobDelete = new SqlCommand(sqlJobDelete, cn);
                        cmdJobDelete.ExecuteNonQuery();



                        //Create the Job
                        String sqlJobCreate = @"USE [msdb]
                                        BEGIN TRANSACTION
                                    DECLARE @ReturnCode INT
                                    SELECT @ReturnCode = 0

                            IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
                            BEGIN
                            EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
                            IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

                            END

                            DECLARE @jobId BINARY(16)
                            EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name='" + jobName.Trim() + @"', @enabled=1, 
		                    @notify_level_eventlog=0, 
		                    @notify_level_email=0, 
		                    @notify_level_netsend=0, 
		                    @notify_level_page=0, 
		                    @delete_level=0, 
		                    @description=N' This jobs is created to capture Trace for database  " + database.DatabaseName + @"', 
		                    @category_name=N'[Uncategorized (Local)]', 
		                    @owner_login_name=N'sa', @job_id = @jobId OUTPUT
                            IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

                            Declare @tempFolder VarChar(256)
                            Declare @tracefile varchar(256)
                            
                            Declare @commandJobStep varchar(4000)
                            Declare @env Table ( [Parts] VarChar(256) )
                            Insert Into @env 
                            Exec Master..Xp_CmdShell 'set' 
                            Set @tempFolder = (  Select Top 1 [Parts] From @env Where [Parts] Like 'temp=%')
                            Set @tempFolder = Replace(@tempFolder , 'temp=','' )
                            Set @tracefile = @tempFolder +  N'\" + jobName.Trim() + @"_Capture_Trace.out'

                            Set @commandJobStep =N'
                                            Declare @Filename nvarchar(250)
                                            Declare @DatabaseName nvarchar(300)
                                            Declare @StopTime datetime

                                            Set @Filename= ''' + @tempFolder + '\HealthCheck_Trace_" + jobName.Trim() + @"''  
                                            Set @DatabaseName = """ + database.DatabaseName + @"""
                                            set @StopTime=""" + strStopTime + @"""

                                            DECLARE @rc int, @dbaTraceID int 
                                                EXEC @rc = master.dbo.Proc_CaptureTrace  @StopTime,@DatabaseName,@Filename,@dbaTraceID  OUTPUT 
                                                '
                                EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StartTrace by Executing Stored Procedure', 
		                                @step_id=1, 
		                                @cmdexec_success_code=0, 
		                                @on_success_action=3, 
		                                @on_success_step_id=0, 
		                                @on_fail_action=3, 
		                                @on_fail_step_id=0, 
		                                @retry_attempts=0, 
		                                @retry_interval=0, 
		                                @os_run_priority=0, @subsystem=N'TSQL', 
		                                @command=@commandJobStep, 
		                                @database_name=N'master', 
		                                @output_file_name= @tracefile, 
		                                @flags=0
										                                EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete Job after Completion', 
		                                @step_id=2, 
		                                @cmdexec_success_code=0, 
		                                @on_success_action=1, 
		                                @on_success_step_id=0, 
		                                @on_fail_action=2, 
		                                @on_fail_step_id=0, 
		                                @retry_attempts=0, 
		                                @retry_interval=0, 
		                                @os_run_priority=0, @subsystem=N'TSQL', 
		                                @command=N'use master
IF EXists(select * from (select count(name) as a from msdb.dbo.sysjobs where name like ''Capture_Trace%'' ) as b where a<2) 
										begin
 DROP PROCEDURE dbo.Proc_CaptureTrace
end
use msdb 
EXEc sp_delete_job @job_name = " + jobName.Trim() + @"'

, 
		                                @database_name=N'master', 
		                                @output_file_name= @tracefile, 
		                                @flags=0
                                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
                                EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
                                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
                                EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule for Job', 
		                                @enabled=1, 
		                                @freq_type=1, 
		                                @freq_interval=0, 
		                                @freq_subday_type=0, 
		                                @freq_subday_interval=0, 
		                                @freq_relative_interval=0, 
		                                @freq_recurrence_factor=0, 
		                                @active_start_date=" + strStartdate + @",
                                        @active_end_date=99991231,@active_start_time=" + strStartTime + @",  
                                        @active_end_time=" + strEndTime + @", 
		                             @schedule_id=148
                                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
                                EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
                                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
                                COMMIT TRANSACTION
                                GOTO EndSave
                                QuitWithRollback:
                                    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
                                EndSave:


                                ";


                        SqlCommand cmdJobCreate = new SqlCommand(sqlJobCreate, cn);
                        cmdJobCreate.ExecuteNonQuery();
                        string sqlOLE3 = "EXEC sp_configure 'xp_cmdshell', '" + settings[1] + "' RECONFIGURE EXEC sp_configure 'show advanced options', '" + settings[0] + "' RECONFIGURE";


                        SqlCommand OLEcmd3 = new SqlCommand(sqlOLE3, cn);
                        OLEcmd3.ExecuteNonQuery();


                    }


                }

                else
                {
                    //Add the name of SQL Intances for which connections were not sucessfull and details were not imported

                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_SQLAgentCheckLog.txt", "FillDatabaseTableDetails: ", " There were problems in importing PerformanceCheck details for " + service.InstanceName.ToString() + ".Please check the connectivity to this instance");
                }


            }
            catch (System.Security.SecurityException ex1)
            {
                Logger.Error(ex1);
                errors.Add(ex1);
            }

            return databaseTraceId;

        }


        private static string AddPrefixtoString(string STR1)
        {
            if (STR1.Trim().Length == 1)
            {
                return "0" + STR1;
            }

            return STR1;
        }

        // Delete a job from a remote server.passing the databaseTrace as a parameter.
        public static void DeleteScheduledTraceJobfromServer(DatabaseTrace databaseTrace)
        {
            try
            {
                string remoteServer = databaseTrace.Database.Service.GetConnectionString("Master");
                string jobName = "Capture_Trace_" + databaseTrace.DatabaseTraceId.ToString();
                //Checks if remote server of trace is accessible
                if (CollectServerAndServiceData.IsSQLServerAccessible(remoteServer))
                {
                    // if it is accessible open a connection to the remote server.
                    using (SqlConnection cn = new SqlConnection(remoteServer))
                    {
                        cn.Open();
                        string strDeletejob = @"DECLARE @jobId uniqueidentifier
                                                SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = @jobName)
                                                    IF (@jobId IS NOT NULL)
                                                BEGIN
                                                    EXEC msdb.dbo.sp_delete_job @jobId
                                                END";
                        SqlCommand ocmdDeleteJob = new SqlCommand(strDeletejob, cn);
                        ocmdDeleteJob.Parameters.Add("@jobName", SqlDbType.VarChar);
                        ocmdDeleteJob.Parameters["@jobName"].Value = jobName;
                        ocmdDeleteJob.ExecuteNonQuery();

                    }

                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex);

            }
        }


        //this method import the trace details from a remote server.The trace file is stored in the remote server

        public static void ImportTraceDetailsfromServer(DatabaseTrace databaseTrace)
        {

            try
            {


                //Open conection to the SQLServerHealthCheck Deploy database
                using (SqlConnection cnHealthCheck = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]))
                {
                    cnHealthCheck.Open();

                    string remoteServer = databaseTrace.Database.Service.GetConnectionString("Master");

                    //Checks if remote server of trace is accessible
                    if (CollectServerAndServiceData.IsSQLServerAccessible(remoteServer))
                    {

                        //check if remote server is already added as linked server,if so drop it
                        string strquery = @"
                            IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'IMPORT_TRACE_DETAILS')   
                                EXEC master.dbo.sp_dropserver @server=N'IMPORT_TRACE_DETAILS', @droplogins='droplogins'";

                        SqlCommand cmdlinkedserver = new SqlCommand(strquery, cnHealthCheck);
                        cmdlinkedserver.ExecuteNonQuery();

                        //adds the  remote server as  linked server
                        strquery = @"EXEC master.dbo.sp_addlinkedserver @server = N'IMPORT_TRACE_DETAILS', @srvproduct=N'SQLServer', 
                                                @provider=N'SQLNCLI', @datasrc=N'" + databaseTrace.Database.Service.InstanceName + "'";
                        cmdlinkedserver.CommandText = strquery;
                        cmdlinkedserver.ExecuteNonQuery();

                        //adds the linked server login
                        strquery = @"EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'IMPORT_TRACE_DETAILS',@useself=N'True',
                                                @locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL";
                        cmdlinkedserver.CommandText = strquery;
                        cmdlinkedserver.ExecuteNonQuery();

                        //Open connnection to the remote server    
                        using (SqlConnection cnremote = new SqlConnection(remoteServer))
                        {
                            cnremote.Open();

                            //if exists drop  the  table in remote server to hold trace details

                            string strremotequery = @"
                                IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseTraceDetails_Remote]') AND type in (N'U'))
                                    DROP TABLE [dbo].[DatabaseTraceDetails_Remote]";

                            SqlCommand cmdremoteserver = new SqlCommand(strremotequery, cnremote);
                            cmdremoteserver.ExecuteNonQuery();

                            //creates a table in remote server master database to hold trace details
                            strremotequery = @"CREATE TABLE [dbo].[DatabaseTraceDetails_Remote](
	                                    	       [EventClass] [nvarchar](250) NULL,
	                                                [TextData] [ntext] NULL,
	                                                [ApplicationName] [nvarchar](128) NULL,
	                                                [NTUserName] [nvarchar](128) NULL,
	                                                [LoginName] [nvarchar](128) NULL,
	                                                [CPU] [int] NULL,
	                                                [Reads] [bigint] NULL,
	                                                [Writes] [bigint] NULL,
	                                                [Duration] [bigint] NULL,
	                                                [ClientProcessID] [int] NULL,
	                                                [SPID] [int] NULL,
	                                                [StartTime] [datetime] NULL, 
	                                                [EndTime] [datetime] NULL,
	                                                [ServerName] [nvarchar](256) NULL,
	                                                [DatabaseName] [nvarchar](256) NULL,
	                                                [ObjectName] [nvarchar](256) NULL,
	                                                [HostName] [nvarchar](256) NULL
                                                   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";

                            cmdremoteserver.CommandText = strremotequery;
                            cmdremoteserver.ExecuteNonQuery();

                            //import the data from file in table DatabaseTraceDetails_Remote in remote server table

                            strremotequery = string.Format(
                               @"
                                
                                Declare @tempFolder VarChar(256)
                                Declare @env Table ( [Parts] VarChar(256) )
declare
@oldvalue int;
declare
@oldvalue2 int;
                               
                            
set @oldvalue =  CONVERT(int, (select value from   sys.configurations
where name = 'show advanced options'));
set @oldvalue2 =  CONVERT(int, (select value from   sys.configurations
where name = 'xp_cmdshell'));

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE

 Insert Into @env 
                                Exec Master..Xp_CmdShell 'set' 
                                Set @tempFolder = (  Select Top 1 [Parts] From @env Where [Parts] Like 'temp=%')
                                Set @tempFolder = Replace(@tempFolder , 'temp=','' )


                                Declare @Filename nvarchar(250)
                                Declare @SQL varchar(8000)
                                set  
                                    @Filename= 'HealthCheck_Trace_Capture_Trace_{0}.trc'  
                               
                                
                                set @SQL='  INSERT INTO  
                                                DatabaseTraceDetails_Remote 
                                            SELECT 
                                                EventClass,TextData,ApplicationName,
                                                NTUserName,LoginName,CPU,Reads,Writes,Duration,
                                                ClientProcessID,SPID,StartTime,EndTime,ServerName,DatabaseName,ObjectName,HostName
                                            FROM 
                                                fn_trace_gettable('''+ @tempFolder + '\'+ @Filename +''',default)'

                                         exec(@SQL)
EXEC sp_configure 'xp_cmdshell',  @oldvalue2
EXEC sp_configure 'show advanced options', @oldvalue
RECONFIGURE
",
                                                 databaseTrace.DatabaseTraceId.ToString());

                            cmdremoteserver.CommandText = strremotequery;
                            cmdremoteserver.ExecuteNonQuery();


                            //delete the details if exists previously in  for same DatabaseTraceId
                            strquery = @"delete from  DatabaseTraceDetails where DatabaseTraceId=" + databaseTrace.DatabaseTraceId.ToString();
                            cmdlinkedserver.CommandText = strquery;
                            cmdlinkedserver.Connection = cnHealthCheck;
                            cmdlinkedserver.ExecuteNonQuery();

                            //inserts into DatabaseTraceDetails in SQLServerHealthCheckDeploy database in destination
                            strquery = string.Format(
                                                @"INSERT INTO  
                                                        DatabaseTraceDetails(DatabaseTraceId,EventClass,TextData,ApplicationName,NTUserName,LoginName,CPU,Reads,Writes,Duration,
                                                        ClientProcessID,SPID,StartTime,EndTime,ServerName,DatabaseName,ObjectName,HostName) 
                                                SELECT 
                                                      {0},EventClass,TextData,ApplicationName,NTUserName,LoginName,CPU,Reads,Writes,Duration,
                                                                 ClientProcessID,SPID,StartTime,EndTime,ServerName,DatabaseName,ObjectName,HostName 
                                                FROM 
                                                    IMPORT_TRACE_DETAILS.master.dbo.DatabaseTraceDetails_Remote", databaseTrace.DatabaseTraceId.ToString());

                            cmdlinkedserver.CommandText = strquery;
                            cmdlinkedserver.Connection = cnHealthCheck;
                            cmdlinkedserver.ExecuteNonQuery();


                        }
                    }
                    else
                    {
                        MessageBox.Show("Couldn't connect to SQL Server Instance :" + databaseTrace.Database.Service.InstanceName + "  .Please check the connectivity", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }

            catch (Exception ex1)
            {
                Logger.Error(ex1);
                MessageBox.Show(ex1.Message.ToString(), "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }


        }

        public static void NormalizeTextData(DatabaseTrace databaseTrace)
        {
            try
            {
                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);

                // Create a link Query ( query Selects a database that has the MDF file stored on the C Drive 
                var qry = from t in dc.DatabaseTraces
                          from td in t.DatabaseTraceDetails
                          where t.DatabaseTraceId == databaseTrace.DatabaseTraceId
                          select td;

                foreach (var detail in qry)
                {
                    if (!string.IsNullOrEmpty(detail.TextData) && !string.IsNullOrEmpty(detail.EventClass) && detail.SPID.HasValue)
                    {
                        String normalizedSQL = NormalizeSqlText(detail.TextData, detail.EventClass, detail.SPID.Value);
                        detail.NormalizedTextData = normalizedSQL;
                    }
                }

                dc.SubmitChanges();
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }

        private static string FixComments(string sql)
        {
            string str = sql;
            int num = 0;
            int startat = 0;
            Match match = _delimiterStart.Match(sql, startat);
            while (match.Success)
            {
                string str4 = match.Value;
                if (str4 == null)
                {
                    return sql;
                }
                if (!(str4 == "'"))
                {
                    if (str4 != "--")
                    {
                        if (str4 != "/*")
                        {
                            return sql;
                        }
                        goto Label_00C7;
                    }
                }
                else
                {
                    Match match2 = _stringConstant.Match(sql, match.Index);
                    if (match2.Success)
                    {
                        startat = match.Index + match2.Length;
                    }
                    else
                    {
                        startat++;
                    }
                    goto Label_00E3;
                }
                startat = match.Index;
                if (_inlineComment.Match(sql, startat).Success)
                {
                    sql = _inlineComment.Replace(sql, "", 1, startat);
                }
                else
                {
                    startat++;
                }
                goto Label_00E3;
            Label_00C7:
                startat = match.Index;
                sql = RemoveBlockComments(sql, startat);
                startat++;
            Label_00E3:
                if (startat >= sql.Length)
                {
                    return sql;
                }
                match = _delimiterStart.Match(sql, startat);
                num++;
                if (num > 0x2710)
                {
                    string str2 = Assembly.GetExecutingAssembly().GetName().Version.ToString();
                    throw new SqlParsingException(("Infinite loop in FixComments (" + str2 + ")" + Environment.NewLine + Environment.NewLine) + str);
                }
            }
            return sql;
        }



        public static string NormalizeSqlText(string sql, string eventClass, int spid)
        {
            return NormalizeSqlText(sql, eventClass, spid, true);
        }


        public static string NormalizeSqlText(string sql, string eventClass, int spid, bool spreadCsv)
        {
            Match match;
            if (sql == null)
            {
                return "";
            }
            bool flag = false;
            bool flag2 = false;
            int num = 0;
            string key = "";
            if (sql == "sp_reset_connection")
            {
                return "";
            }
            sql = FixComments(sql);
            sql = _spaces.Replace(sql, " ").ToUpper(CultureInfo.InvariantCulture);
            if ((eventClass == "RPC:Completed") || (eventClass == "RPC:Started"))
            {
                sql = _doubleApostrophe.Replace(sql, "{STR}");
                match = _prepExecRpc.Match(sql);
                if (match.Success)
                {
                    sql = match.Groups["statement"].ToString();
                }
                match = _prepareSql.Match(sql);
                if (match.Success)
                {
                    if (match.Groups["preptype"].ToString().ToLower() == "sp_prepare")
                    {
                        flag2 = true;
                    }
                    if (match.Groups["stmtnum"].ToString() == "NULL")
                    {
                        num = 0;
                    }
                    else
                    {
                        num = Convert.ToInt32(match.Groups["stmtnum"].ToString());
                    }
                    sql = match.Groups["remaining"].ToString();
                    match = _preppedSqlStatement.Match(sql);
                    if (match.Success)
                    {
                        sql = match.Groups["statement"].ToString();
                        sql = _doubleApostrophe.Replace(sql, "'${string}'");
                    }
                    flag = true;
                }
                match = null;
                match = _execPrepped.Match(sql);
                if (match.Success)
                {
                    num = Convert.ToInt32(match.Groups["stmtnum"].ToString());
                    if (prepSql.ContainsKey(spid.ToString() + "_" + num.ToString()))
                    {
                        return TruncateSql("{PREPARED} " + prepSql[spid.ToString() + "_" + num.ToString()].ToString());
                    }
                }
                match = null;
                match = _execUnprep.Match(sql);
                if (match.Success)
                {
                    num = Convert.ToInt32(match.Groups["stmtnum"].ToString());
                    key = spid.ToString() + "_" + num.ToString();
                    if (prepSql.ContainsKey(key))
                    {
                        sql = prepSql[key].ToString();
                        prepSql.Remove(spid.ToString() + "_" + num.ToString());
                        match = null;
                        return TruncateSql("{UNPREPARING} " + sql);
                    }
                }
                match = null;
            }
            if ((eventClass == "RPC:Completed") || (eventClass == "RPC:Started"))
            {
                match = _cursor.Match(sql);
                if (match.Success)
                {
                    sql = "{CURSOR} " + match.Groups["statement"].ToString();
                    sql = _doubleApostrophe.Replace(sql, "'${string}'");
                }
                match = _cursorPrepExec.Match(sql);
                if (match.Success)
                {
                    sql = "{CURSOR} " + match.Groups["statement"].ToString();
                    sql = _doubleApostrophe.Replace(sql, "'${string}'");
                }
                match = _spExecuteSql.Match(sql);
                if (match.Success)
                {
                    sql = match.Groups["statement"].ToString();
                }
                match = null;
                if (!_brackets.Match(sql).Success)
                {
                    match = _dbAndObjectName.Match(sql);
                    if (match.Success)
                    {
                        sql = match.Groups["object"].ToString();
                    }
                    else
                    {
                        match = _objectName.Match(sql);
                        if (match.Success)
                        {
                            sql = match.Groups["object"].ToString();
                        }
                    }
                    if (((sql == "SP_CURSOR") || (sql == "SP_CURSORFETCH")) || ((sql == "SP_CURSORCLOSE") || (sql == "SP_RESET_CONNECTION")))
                    {
                        match = null;
                        return "";
                    }
                }
                match = null;
            }
            sql = _emptyString.Replace(sql, "{STR}");
            sql = _stringConstant.Replace(sql, "{STR}");
            sql = _unicodeConstant.Replace(sql, "{NSTR}");
            sql = _binaryConstant.Replace(sql, "{BINARY}");
            sql = _numericConstant.Replace(sql, "${prefix}{##}");
            sql = _inClause.Replace(sql, "{IN}");
            if (spreadCsv)
            {
                sql = _spreadCsv.Replace(sql, ", ");
            }
            sql = _spaces.Replace(sql, " ");
            sql = TruncateSql(sql);
            if (flag && (num != 0))
            {
                if (!prepSql.ContainsKey(spid.ToString() + "_" + num.ToString()))
                {
                    prepSql.Add(spid.ToString() + "_" + num.ToString(), sql);
                }
                else
                {
                    prepSql[spid.ToString() + "_" + num.ToString()] = sql;
                }
            }
            match = null;
            if (flag2)
            {
                return TruncateSql("{PREPARING} " + sql);
            }
            if (flag && !flag2)
            {
                return TruncateSql("{PREPARED} " + sql);
            }
            return TruncateSql(sql);
        }

        private static string RemoveBlockComments(string sql, int position)
        {
            StringBuilder builder = new StringBuilder(sql.Length);
            builder.Append(sql.Substring(0, position));
            int num = 0;
            int startIndex = position;
            while (startIndex < (sql.Length - 1))
            {
                string str = sql.Substring(startIndex, 2);
                if (str == null)
                {
                    goto Label_0065;
                }
                if (!(str == "/*"))
                {
                    if (str == "*/")
                    {
                        goto Label_0057;
                    }
                    goto Label_0065;
                }
                num++;
                startIndex++;
                startIndex++;
                goto Label_0069;
            Label_0057:
                num--;
                startIndex++;
                startIndex++;
                goto Label_0069;
            Label_0065:
                startIndex++;
            Label_0069:
                if (num == 0)
                {
                    if (startIndex < sql.Length)
                    {
                        builder.Append(sql.Substring(startIndex, sql.Length - startIndex));
                    }
                    return builder.ToString();
                }
            }
            return sql;
        }

        private static string TruncateSql(string sql)
        {
            sql = sql.Trim();
            if (sql.Length > 0xfa0)
            {
                return sql.Substring(0, 0xfa0);
            }
            return sql;
        }














        //this method imports the trace details from the selected trace file. The trace file is stored in local host
        public static void ImportTraceDetailsfromFile(string strFileName)
        {
            try
            {
                long lngTraceFile_id = 0;

                //Open conection to the SQLServerHealthCheck Deploy database
                SqlConnection cnHealthCheck = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
                cnHealthCheck.Open();


                //insert the details of Trace File into TraceFile table
                string strTraceFile = "insert into Trace_File(StartTime,TraceFileName) values('" + DateTime.Now + "','" + strFileName + "')";
                SqlCommand ocmdTrace = new SqlCommand(strTraceFile, cnHealthCheck);
                ocmdTrace.ExecuteNonQuery();

                //selects the last generated trace id
                strTraceFile = "select SCOPE_IDENTITY()";
                ocmdTrace.CommandText = strTraceFile;
                lngTraceFile_id = Convert.ToInt64(ocmdTrace.ExecuteScalar().ToString());

                //Reads the trace file and inserts the details into TraceDetails_File table
                TraceFile trcFile = new TraceFile();
                trcFile.InitializeAsReader(strFileName);
                while (trcFile.Read())
                {

                    strTraceFile = CreateInsertQueryfromTraceFile(trcFile, lngTraceFile_id);//Creates the insert query
                    ocmdTrace.CommandText = strTraceFile;
                    MessageBox.Show(strTraceFile);
                    ocmdTrace.ExecuteNonQuery();

                }

                //updates the end time of selected Trace file in  TraceFile table
                strTraceFile = "update Trace_File set EndTime='" + DateTime.Now + "' where TraceFileId=" + lngTraceFile_id;
                ocmdTrace.CommandText = strTraceFile;
                ocmdTrace.ExecuteNonQuery();
                trcFile.Close();
                MessageBox.Show("The details about trace Imported", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            catch (Exception ex1)
            {

                MessageBox.Show(ex1.Message.ToString(), "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }


        }

        //this method  generates the insert query for importing the  details from Trace File
        private static string CreateInsertQueryfromTraceFile(TraceFile trcFile, long lngTraceFile_id)
        {

            String strquerycols = " insert into TraceDetails_File(TraceFileId,EventClass";
            String strvalues = "values(" + lngTraceFile_id + ",'" + trcFile.GetString(trcFile.GetOrdinal("EventClass")) + "'";

            //If TextData col is not null

            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("TextData"))))
            {
                strquerycols = strquerycols + ",TextData";

                string strTextData = trcFile.GetString(trcFile.GetOrdinal("TextData")).ToString();
                strTextData = strTextData.Replace("'", "\"");
                strvalues = strvalues + ",'" + strTextData + "'";
            }

            //If ApplicationName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("ApplicationName"))))
            {
                strquerycols = strquerycols + ",ApplicationName";
                strvalues = strvalues + ",'" + trcFile.GetString(trcFile.GetOrdinal("ApplicationName")).ToString() + "'";
            }

            //If NTUserName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("NTUserName"))))
            {
                strquerycols = strquerycols + ",NTUserName";
                strvalues = strvalues + ",'" + trcFile.GetString(trcFile.GetOrdinal("NTUserName")).ToString() + "'";
            }

            //If LoginName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("LoginName"))))
            {
                strquerycols = strquerycols + ",LoginName";
                strvalues = strvalues + ",'" + trcFile.GetString(trcFile.GetOrdinal("LoginName")).ToString() + "'";
            }

            //If CPU col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("CPU"))))
            {
                strquerycols = strquerycols + ",CPU";
                strvalues = strvalues + "," + trcFile.GetInt32(trcFile.GetOrdinal("CPU")).ToString();
            }

            //If Reads col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("Reads"))))
            {
                strquerycols = strquerycols + ",Reads";
                strvalues = strvalues + "," + trcFile.GetInt64(trcFile.GetOrdinal("Reads")).ToString();
            }

            //If Writes col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("Writes"))))
            {
                strquerycols = strquerycols + ",Writes";
                strvalues = strvalues + "," + trcFile.GetInt64(trcFile.GetOrdinal("Writes")).ToString();
            }

            //If Duration col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("Duration"))))
            {
                strquerycols = strquerycols + ",Duration";
                strvalues = strvalues + "," + trcFile.GetInt64(trcFile.GetOrdinal("Duration")).ToString();
            }

            //If ClientProcessID col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("ClientProcessID"))))
            {
                strquerycols = strquerycols + ",ClientProcessID";
                strvalues = strvalues + "," + trcFile.GetInt32(trcFile.GetOrdinal("ClientProcessID")).ToString();
            }


            //If SPID col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("SPID"))))
            {
                strquerycols = strquerycols + ",SPID";
                strvalues = strvalues + "," + trcFile.GetInt32(trcFile.GetOrdinal("SPID")).ToString();
            }

            //If StartTime col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("StartTime"))))
            {
                strquerycols = strquerycols + ",StartTime";
                strvalues = strvalues + ",'" + trcFile.GetDateTime(trcFile.GetOrdinal("StartTime")) + "'";
            }

            //If EndTime col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("EndTime"))))
            {
                strquerycols = strquerycols + ",EndTime";
                strvalues = strvalues + ",'" + trcFile.GetDateTime(trcFile.GetOrdinal("EndTime")) + "'";
            }

            //If ServerName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("ServerName"))))
            {
                strquerycols = strquerycols + ",ServerName";
                strvalues = strvalues + ",'" + trcFile.IsDBNull(trcFile.GetOrdinal("ServerName")) + "'";
            }


            //If DatabaseName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("DatabaseName"))))
            {
                strquerycols = strquerycols + ",DatabaseName";
                strvalues = strvalues + ",'" + trcFile.IsDBNull(trcFile.GetOrdinal("DatabaseName")) + "'";
            }

            //If ObjectName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("ObjectName"))))
            {
                strquerycols = strquerycols + ",ObjectName";
                strvalues = strvalues + ",'" + trcFile.IsDBNull(trcFile.GetOrdinal("ObjectName")) + "'";
            }


            //If HostName col is not null
            if (!(trcFile.IsDBNull(trcFile.GetOrdinal("HostName"))))
            {
                strquerycols = strquerycols + ",HostName";
                strvalues = strvalues + ",'" + trcFile.IsDBNull(trcFile.GetOrdinal("HostName")) + "'";
            }



            return strquerycols + ")" + strvalues + ")";
        }

        public static void ImportTraceDetails(SQLServerInstance instance, string databaseName)
        {

            try
            {
                //Chekcs if source server of trace is accessible
                if (CollectServerAndServiceData.IsSQLServerAccessible(instance.GetConnectionString("master")))
                {

                    //Open connection to the SQLServerHealthCheck Deploy database
                    SqlConnection cnHealthCheck = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
                    cnHealthCheck.Open();


                    //check if source server is already added as linked server,if so drop it
                    string strquery = @"IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'IMPORT_TRACE_DETAILS')EXEC master.dbo.sp_dropserver @server=N'IMPORT_TRACE_DETAILS', @droplogins='droplogins'";
                    SqlCommand cmdlinkedserver = new SqlCommand(strquery, cnHealthCheck);
                    cmdlinkedserver.ExecuteNonQuery();

                    //adds the  source server as  linked server
                    strquery = @"EXEC master.dbo.sp_addlinkedserver @server = N'IMPORT_TRACE_DETAILS', @srvproduct=N'SQLServer', @provider=N'SQLNCLI', @datasrc=N'" + instance.InstanceName + "'";
                    cmdlinkedserver.CommandText = strquery;
                    cmdlinkedserver.ExecuteNonQuery();

                    //adds the linked server login
                    strquery = @"EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'IMPORT_TRACE_DETAILS',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL";
                    cmdlinkedserver.CommandText = strquery;
                    cmdlinkedserver.ExecuteNonQuery();

                    //Open connnection to the source server    
                    using (SqlConnection cnsource = new SqlConnection(instance.GetConnectionString("master")))
                    {
                        cnsource.Open();

                        //if exists drop  the  table in source server to hold trace details

                        string strsourcequery = @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trace_HealthCheck]') AND type in (N'U'))
                            DROP TABLE [dbo].[Trace_HealthCheck]";
                        SqlCommand cmdsourceserver = new SqlCommand(strsourcequery, cnsource);
                        cmdsourceserver.ExecuteNonQuery();

                        //creates a table in source server master database to hold trace details
                        strsourcequery = @"CREATE TABLE [dbo].[Trace_HealthCheck](
	                                    	    [EventClass] [int] NULL,
	                                            [TextData] [ntext] NULL,
	                                            [ApplicationName] [nvarchar](128) NULL,
	                                            [NTUserName] [nvarchar](128) NULL,
	                                            [LoginName] [nvarchar](128) NULL,
	                                            [CPU] [int] NULL,
	                                            [Reads] [bigint] NULL,
	                                            [Writes] [bigint] NULL,
	                                            [Duration] [bigint] NULL,
	                                            [ClientProcessID] [int] NULL,
	                                            [SPID] [int] NULL,
	                                            [StartTime] [datetime] NULL,
	                                            [EndTime] [datetime] NULL,
	                                            [BinaryData] [image] NULL,
                                               ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";

                        cmdsourceserver.CommandText = strsourcequery;
                        cmdsourceserver.ExecuteNonQuery();

                        //import the data from file in table Trace_HealthCheck in source server table

                        strsourcequery = @"
                            Declare @Filename nvarchar(250)
                            Declare @SQL varchar(8000)
                            select @Filename= SUBSTRING([filename],0, CHARINDEX([name],[filename],0))+'HealthCheck_Trace.trc'  from sys.sysaltfiles where dbid=1 and fileid=1
                            set @SQL='INSERT INTO  Trace_HealthCheck SELECT EventClass,TextData,ApplicationName,NTUserName,LoginName,CPU,Reads,Writes,Duration,
                                        ClientProcessID,SPID,StartTime,EndTime,BinaryData
                                      FROM fn_trace_gettable('''+ @Filename +''',default)'
                                     exec(@SQL) ";

                        cmdsourceserver.CommandText = strsourcequery;
                        cmdsourceserver.ExecuteNonQuery();


                    }


                    //inserts into Trace_HealthCheck in SQLServerHealthCheckDeploy database in destination
                    strquery = @"INSERT INTO  Trace_HealthCheck(EventClass,TextData,ApplicationName,NTUserName,LoginName,CPU,Reads,Writes,Duration,
                                        ClientProcessID,SPID,StartTime,EndTime,BinaryData) SELECT EventClass,TextData,ApplicationName,NTUserName,LoginName,CPU,Reads,Writes,Duration,
                                        ClientProcessID,SPID,StartTime,EndTime,BinaryData FROM IMPORT_TRACE_DETAILS.master.dbo.Trace_HealthCheck";
                    cmdlinkedserver.CommandText = strquery;
                    cmdlinkedserver.Connection = cnHealthCheck;
                    cmdlinkedserver.ExecuteNonQuery();


                }

                else
                {

                    errors.Add(new Exception("Couldn't connect to SQL Server Instance :" + instance.InstanceName + "."));
                }
            }

            catch (Exception ex1)
            {

                errors.Add(ex1);
            }


        }


    }

}
